﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BlockMe.Infrastructure;
using BlockMe.Model;
using Alpha.DataBase;
using MySql.Data.MySqlClient;
using System.Data;

namespace BlockMe.Repository.MySQL
{
    public class PostRepository : IPostRepository
    {
        DBProvider provider = ProviderFactory.GetProvider();

        public string PostTableName { get; set; }

        public string CommentTableName { get; set; }

        public void CreateCommentTable()
        {
            string sql = "CREATE TABLE IF NOT EXISTS `" + CommentTableName + "` (";
            sql += "`commentid` int(10) NOT NULL AUTO_INCREMENT,";
            sql += "`comment_content` text NOT NULL,";
            sql += "`postid` int(10) NOT NULL DEFAULT '0',";
            sql += "`comment_author` varchar(50) NOT NULL DEFAULT '0',";
            sql += "`comment_email` varchar(50) NOT NULL DEFAULT '0',";
            sql += "`comment_host` varchar(100) DEFAULT '0',";
            sql += "`comment_approved` bit(1) NOT NULL,";
            sql += "`comment_postdate` datetime NOT NULL,";
            sql += "`comment_postdate_gmt` datetime NOT NULL,";
            sql += "PRIMARY KEY (`commentid`)";
            sql += ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
            provider.ExecuteNonQuery(sql);
        }

        public void CreatePostTable()
        {
            string sql = "CREATE TABLE IF NOT EXISTS `" + PostTableName + "` (";
            sql += "`postid` int(10) NOT NULL AUTO_INCREMENT,";
            sql += "`title` varchar(255) NOT NULL,";
            sql += "`authorid` int(11) NOT NULL,";
            sql += "`content` longtext NOT NULL,";
            sql += "`publishdate` datetime NOT NULL,";
            sql += "`publishdate_gmt` datetime NOT NULL,";
            sql += "`post_status` enum('Published','Draft','Trash') NOT NULL,";
            sql += "`author_name` varchar(250) NOT NULL,";
            sql += "`modified_date` datetime DEFAULT NULL,";
            sql += "`modified_date_gmt` datetime DEFAULT NULL,";
            sql += "`comment_count` int(10) NOT NULL DEFAULT '0',";
            sql += "PRIMARY KEY (`postid`),";
            sql += "KEY `AuthorID` (`authorid`),";
            sql += "KEY `PublishDate` (`publishdate`),";
            sql += "KEY `AuthorName` (`author_name`)";
            sql += ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
            provider.ExecuteNonQuery(sql);
        }

        public PostRepository()
        {
            PostTableName = "posts";
            CommentTableName = "comments";
        }

        public IEnumerable<Post> GetPostsByDate(DateTime time)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Post> GetPostsByTag(string tag)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Comment> GetPostComment(int postID)
        {
            var reader = provider.ExecuteDataReader
                ("select comment_id,comment_content,comment_author,comment_email,comment_host,comment_postdate from " + CommentTableName +  " where comment_approved=true and postid=" + postID);
            var list = new List<Comment>();
            if (!reader.HasRows)
            {
                return list;
            }
            while (reader.Read())
            {
                var comment = new Comment { 
                    CommentID = reader.GetInt32(0),
                    Content = reader.GetString(1),
                    Author = reader.GetString(2),
                    Email = reader.GetString(3),
                    Host = reader.IsDBNull(4)?string.Empty:reader.GetString(4),
                    PostDate = reader.GetDateTime(5),
                    PostID = postID
                };
                list.Add(comment);
            }
            reader.Close();
            return list;
        }

        public IEnumerable<Post> GetAll()
        {
            throw new NotImplementedException();
        }

        public void Delete(Post item)
        {
            throw new NotImplementedException();
        }

        public Post Add(Post item)
        {
            var sqlParams = new MySqlParameter[] {
                new MySqlParameter{ ParameterName = "@title",Value = item.Title, DbType = DbType.String},
                new MySqlParameter{ ParameterName = "@authorid",Value = item.AuthorID, DbType = DbType.Int32},
                new MySqlParameter{ ParameterName = "@content",Value = item.Content, DbType = DbType.String},
                new MySqlParameter{ ParameterName = "@publishdate",Value = item.PublishDate, DbType = DbType.DateTime},
                new MySqlParameter{ ParameterName = "@publishdate_gmt",Value = item.PublishDate.ToUniversalTime(), DbType = DbType.DateTime},
                new MySqlParameter{ ParameterName = "@post_status",Value = item.Status, MySqlDbType = MySqlDbType.Enum},
                new MySqlParameter{ ParameterName = "@author_name",Value = item.Author, DbType = DbType.String},
            };
            item.PostID = provider.ExecuteAndReturnID(
                "insert into " + PostTableName + " (title,authorid,content,publishdate,publishdate_gmt,post_status,author_name) values (@title,@authorid,@content,@publishdate,@publishdate_gmt,@post_status,@author_name)", sqlParams);
            return item;
        }

        public Post Update(Post updateEntity)
        {
            throw new NotImplementedException();
        }

        public int Counter()
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Post> Retrieve(Post condition)
        {
            throw new NotImplementedException();
        }

        public int Count(PostStatus key)
        {
            throw new NotImplementedException();
        }


        public Post GetByTitle(string title)
        {
            throw new NotImplementedException();
        }
    }
}
